14 - Organizational Intelligence

An international hotel chain has asked you to design a multidimensional database for its marketing department. What identifier and variable dimensions would you select?

Apply the basic prompts outlined in the book.

Identifiers	When	Arrival date and and time Departure date and and time Season level (high, |medium, low) Weather
	Where	Country Region City
	What	Room type Hotel class (stars)
	How	Reservation channel (phone, Web, mobile, fax)
Variables	Outcomes	Duration of stay Room revenue Meals revenue Amenities revenue

---

Compute total payments by country without and with ROLLUP

SELECT DISTINCT country, FORMAT(SUM(amount),0) AS Payments FROM Customers, Payments
    WHERE Customers.customerNumber = Payments.customerNumber
    GROUP BY  country;

SELECT country, FORMAT(SUM(amount),0) AS Payments FROM Customers, Payments
    WHERE Customers.customerNumber = Payments.customerNumber
    GROUP BY  country WITH ROLLUP;

---

Compute total payments by country and year without and with ROLLUP

SELECT DISTINCT country, YEAR(paymentDate) AS year, FORMAT(SUM(amount),0) AS Payments
    FROM Customers, Payments
    WHERE Customers.customerNumber = Payments.customerNumber
    GROUP BY  country, year;

SELECT country, YEAR(paymentDate) AS year, FORMAT(SUM(amount),0) AS Payments
FROM Customers, Payments
WHERE Customers.customerNumber = Payments.customerNumber
GROUP BY  country, year WITH ROLLUP;

---

Compute total value of orders by country and product line without and with ROLLUP

SELECT DISTINCT country AS 'Country', ProductLines.productLine, FORMAT(sum(quantityOrdered*priceEach),0) AS 'Order value'
    FROM Customers, Orders, OrderDetails, Products, ProductLines
    WHERE Customers.customerNumber = Orders.customerNumber
    AND Orders.orderNumber = OrderDetails.orderNumber
    AND OrderDetails.productCode = Products.productCode
    AND Products.productLine = ProductLines.productLine
    GROUP BY  country, ProductLines.productLine, 'Order value';

SELECT country AS 'Country', ProductLines.productLine, FORMAT(sum(quantityOrdered*priceEach),0) AS 'Order value'
FROM Customers, Orders, OrderDetails, Products, ProductLines
WHERE Customers.customerNumber = Orders.customerNumber
AND Orders.orderNumber = OrderDetails.orderNumber
AND OrderDetails.productCode = Products.productCode
AND Products.productLine = ProductLines.productLine
GROUP BY  country, ProductLines.productLine, 'Order value' WITH ROLLUP;